package com.ddddddl.zhangben.data;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class AccountDAO extends SQLiteOpenHelper {

	public AccountDAO(Context context) {
		super(context, "DDZhangben", null, 2);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE " + Columns.Account.T_NAME + "("
				+ Columns.Account.ID + " INTEGER PRIMARY KEY autoincrement,"
				+ Columns.Account.AMOUNT + " REAL,"
				+ Columns.Account.FISRT_TYPE + " INTEGER,"
				+ Columns.Account.SECOND_TYPE + " INTEGER,"
				+ Columns.Account.DATE + " TEXT," 
				+ Columns.Account.NOTE + " TEXT" 
				+ ");");
		db.execSQL("CREATE TABLE " + Columns.Type.T_NAME + "(" 
				+ Columns.Type.ID + " INTEGER PRIMARY KEY autoincrement,"
				+ Columns.Type.NAME + " TEXT,"
				+ Columns.Type.P_ID + " INTEGER" 
				+ ");");
		initType(db);

	}
	
	private void initType(SQLiteDatabase db) {
		long id = addType(new Type("日常"), db);
		addType(new Type("日常一", id), db);
		addType(new Type("日常二", id), db);
		addType(new Type("日常三", id), db);
		id = addType(new Type("生活必备"), db);
		addType(new Type("生活必备一", id), db);
		addType(new Type("生活必备二", id), db);
		addType(new Type("生活必备三", id), db);
		id = addType(new Type("三餐"), db);
		addType(new Type("早餐", id), db);
		addType(new Type("午餐", id), db);
		addType(new Type("晚餐", id), db);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		if (oldVersion == 1) {
			db.execSQL("ALTER TABLE " + Columns.Account.T_NAME + " ADD " + "AAA TEXT DEFAULT ''"  );
		}
	}
	
	public void addAccount(Account account) {
		addAccount(account, null);
	}
	
	public void addAccount(Account account, SQLiteDatabase db) {
		if (db == null) {
			db = this.getWritableDatabase();
		}
		ContentValues values = new ContentValues();
		values.put(Columns.Account.AMOUNT, account.amount);
		values.put(Columns.Account.FISRT_TYPE, account.firstType);
		values.put(Columns.Account.SECOND_TYPE, account.secondType);
		values.put(Columns.Account.DATE, account.date);
		values.put(Columns.Account.NOTE, account.note);
		db.insert(Columns.Account.T_NAME, null, values);
	}
	
	public ArrayList<Account> getAccounts(String startDate, String endDate) {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.query(Columns.Account.T_NAME, null, Columns.Account.DATE + " BETWEEN ? AND ?", new String[] {startDate, endDate}, null, null, Columns.Account.DATE + " DESC");
		ArrayList<Account> result = new ArrayList<Account>();
		while (cursor.moveToNext()) {
			Account account = new Account();
			account.id = cursor.getLong(cursor.getColumnIndex(Columns.Account.ID));
			account.amount = cursor.getDouble(cursor.getColumnIndex(Columns.Account.AMOUNT));
			account.firstType = cursor.getInt(cursor.getColumnIndex(Columns.Account.FISRT_TYPE));
			account.secondType = cursor.getInt(cursor.getColumnIndex(Columns.Account.SECOND_TYPE));
			account.date = cursor.getString(cursor.getColumnIndex(Columns.Account.DATE));
			account.note = cursor.getString(cursor.getColumnIndex(Columns.Account.NOTE));
			result.add(account);
		}
		cursor.close();
		return result;
	}
	
	public long addType(Type type) {
		return addType(type, null);
	}
	
	public long addType(Type type, SQLiteDatabase db) {
		if (db == null) {
			db = this.getWritableDatabase();
		}
		ContentValues values = new ContentValues();
		values.put(Columns.Type.NAME, type.name);
		values.put(Columns.Type.P_ID, type.pID);
		return db.insert(Columns.Type.T_NAME, null, values);
	}
	
	public ArrayList<Type> getTypes() {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.query(Columns.Type.T_NAME, null, null, null, null, null, null);
		ArrayList<Type> result = new ArrayList<Type>();
		while (cursor.moveToNext()) {
			long id = cursor.getLong(cursor.getColumnIndex(Columns.Type.ID));
			String name = cursor.getString(cursor.getColumnIndex(Columns.Type.NAME));
			long pID = cursor.getLong(cursor.getColumnIndex(Columns.Type.P_ID));
			Type type = new Type(name, pID);
			type.id = id;
			result.add(type);
		}
		cursor.close();
		return result;
	}

	public void updateAccount(Account account) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(Columns.Account.AMOUNT, account.amount);
		values.put(Columns.Account.FISRT_TYPE, account.firstType);
		values.put(Columns.Account.SECOND_TYPE, account.secondType);
		values.put(Columns.Account.DATE, account.date);
		values.put(Columns.Account.NOTE, account.note);
		db.update(Columns.Account.T_NAME, values, Columns.Account.ID + " = ? ", new String[] {String.valueOf(account.id)});
	}
	
	public void deleteAccount(Account account) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(Columns.Account.T_NAME, Columns.Account.ID + " = ? ", new String[] {String.valueOf(account.id)});
	}
	
	public void updateType(Type type) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(Columns.Type.NAME, type.name);
		db.update(Columns.Type.T_NAME, values, Columns.Type.ID + " = ? ", new String[] {String.valueOf(type.id)});
	}
	
}
